﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IAC_2013.com.iac.form;
using IAC_2013.com.iac.util;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace IAC_2013.com.iac.action
{
    class UserAction
    {
        SqlConnection conn = new SqlConnection(DBUtils.CONNECTION_STRING);
        SqlCommand cmd;
        SqlDataAdapter da;
        DataTable tb;
        SqlDataReader reader;
        AutoCompleteStringCollection namesCollection;
        
        /*
         * This is function add new User
         * 
         */
        public Boolean addUser(UserForm frm)
        {
            if (frm.positionsId != 0)
            {
                try
                {
                    String sql = "INSERT INTO  nd_nguoi_dung VALUES(";
                    sql += "'"+ frm.code +"', ";
                    sql += "N'"+ frm.name +"', ";
                    sql += frm.positionsId + ", ";
                    sql += 1 + ", ";
                    sql += "'"+ frm.userName +"', ";
                    sql += "'"+ frm.password +"', ";
                    sql += "N'"+ frm.address +"', ";
                    sql += "'"+ frm.phone +"', ";
                    sql += "'"+ frm.email +"')";

                    cmd = new SqlCommand(sql, conn);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /*
         * This is function add new the Postions
         * @author: Nguyen Tien Toi
         * @since: 06/2013
         * 
         */
        public int addPositions(PositionsForm frm)
        {
            int _id = 0;
            try
            {
                frm.code = this.getAutoCode();
                string sql = "INSERT INTO cv_chuc_vu OUTPUT INSERTED.id_cv VALUES(";
                sql += "'" + frm.code + "', ";
                sql += "N'" + frm.name + "', ";
                sql += "N'" + frm.description + "')";

                cmd = new SqlCommand(sql, conn);
                conn.Open();
                _id = (int)cmd.ExecuteScalar();
                conn.Close();

                return _id;
            }
            catch (Exception)
            {
                return _id;
            }
        }

        public String getAutoCode()
        {
            String sql = "SELECT COUNT(*) FROM cv_chuc_vu";
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            reader = cmd.ExecuteReader();
            conn.Close();
            int count = 0;
            while (reader.Read())
            {
                count++;
            }
            count = count + 1;
            String result = "00" + count.ToString();
            return result;
        }

        /*
         * This is function add new the Authority
         * @author: Nguyen Tien Toi
         * @since: 06/2013
         */
        public int addAuthority(AuthorityForm frm)
        {
            int _id = 0;
            try
            {
                string sql = "INSERT INTO qh_phan_quyen OUTPUT INSERTED.id_qh VALUES(";
                sql += "N'"+ frm.name +"', ";
                sql += "N'"+ frm.description +"')";
                
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                _id = (int)cmd.ExecuteScalar();
                conn.Close();

                return _id;
            }
            catch (Exception)
            {
                return _id;
            }
        }

        public AutoCompleteStringCollection getListPositions()
        {
            namesCollection = new AutoCompleteStringCollection();
            String sql = "select distinct [ten_cv], id_cv from [cv_chuc_vu] order by [ten_cv] asc";
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            reader = cmd.ExecuteReader();
            if (reader.HasRows == true)
            {
                while (reader.Read())
                {
                    namesCollection.Add(reader["ten_cv"].ToString());
                    namesCollection.Add(reader["id_cv"].ToString());
                }
            }
            else
            {
                MessageBox.Show("Chức vụ này chưa có, bạn có thể dùng nó để thêm mới.");
            }
            reader.Close();
            conn.Close();
            return namesCollection;
        }

        public AutoCompleteStringCollection searchUserName()
        {
            namesCollection = new AutoCompleteStringCollection();
            String sql = "select distinct [ten_dang_nhap] from [nd_nguoi_dung] order by [ten_dang_nhap] asc";
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            reader = cmd.ExecuteReader();
            if (reader.HasRows == true)
            {
                while (reader.Read())
                {
                    namesCollection.Add(reader["ten_dang_nhap"].ToString());
                }
            }
            reader.Close();
            conn.Close();
            return namesCollection;
        }

        public DataTable listPositions()
        {
            String sql = "SELECT * FROM cv_chuc_vu";
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            da = new SqlDataAdapter(cmd);
            tb = new DataTable();
            da.Fill(tb);
            conn.Close();
            return tb;
        }

    }
}
